# _*_ coding:utf-8 _*_
# @Time  : 2023.03.29
# @Author: zizlee
import datetime
import pathlib
import pandas as pd

from mssql import MSSQLProxy, CJDGLConnection


# from 诊断系统数据处理.database.mssql import MSSQLProxy


def generate_date(s: datetime.date, e: datetime.date):
    date_list = []
    while s <= e:
        date_list.append(s)
        s += datetime.timedelta(days=1)
    return date_list


def make_folders(date_obj: datetime.date = None):
    if date_obj:
        folder = pathlib.Path(date_obj.strftime('%Y%m%d'))
        if not folder.exists():
            folder.mkdir(parents=True)
        return
    folder = pathlib.Path('资金对帐表')
    if not folder.exists():
        folder.mkdir(parents=True)

    folder = pathlib.Path('交易统计表')
    if not folder.exists():
        folder.mkdir(parents=True)

    folder = pathlib.Path('成交明细表')
    if not folder.exists():
        folder.mkdir(parents=True)

    folder = pathlib.Path('平仓明细表')
    if not folder.exists():
        folder.mkdir(parents=True)

    folder = pathlib.Path('成交单管理')
    if not folder.exists():
        folder.mkdir(parents=True)


def download_zjdz(h_date: datetime.date, is_date=False):
    print('{} | 开始下载{}资金对帐表'.format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), h_date.strftime('%Y-%m-%d')))
    if is_date:
        zjdz_file = pathlib.Path('{}/zjdz_{}.csv'.format(h_date.strftime('%Y%m%d'), h_date.strftime('%Y%m%d')))
    else:
        zjdz_file = pathlib.Path('资金对帐表/zjdz_{}.csv'.format(h_date.strftime('%Y%m%d')))
    if zjdz_file.exists():
        print('{}资金对帐表已存在!'.format(h_date.strftime('%Y-%m-%d')))
        return
    with MSSQLProxy() as c:
        c.execute("""
            select
               交易日 as ex_date,客户号 as cust_num,客户名称 as cust_name,上日结存 as sett_previous,出金 as fund_out,
               入金 as fund_in,期末权益 as equity_end,可用资金 as fund_avail,今日结存 as sett_current,客户保证金 as fund_bail,
               交易所保证金 as fund_bail_exl,手续费 as charge,上交手续费 as charge_exl,留存手续费 as charge_odd,净手续费 as charge_net,
               总盈亏 as profit,平仓盈亏_盯 as profit_close, 浮动盈亏_盯 as profit_drift, 浮动盈亏 as profit_fd
            from
                 cust_r
            where
                  交易日=%s
            order by
                     cust_num;
        """, (h_date.strftime('%Y%m%d'),))
        data = list(c.fetchall())
        if len(data) < 1:
            print('无{}资金对帐表数据!'.format(h_date.strftime('%Y-%m-%d')))
            return
        df = pd.DataFrame(data)
        df['cust_name'].fillna('', inplace=True)
        for c1 in ['ex_date', 'cust_num', 'cust_name']:
            df[c1] = df[c1].apply(lambda x: x.strip())
        for c2 in ['sett_previous', 'fund_out', 'fund_in', 'equity_end', 'fund_avail', 'sett_current', 'fund_bail',
                   'fund_bail_exl', 'charge', 'charge_exl', 'charge_odd', 'charge_net', 'profit', 'profit_close',
                   'profit_drift', 'profit_fd']:
            df[c2] = df[c2].astype(float)
        df.to_csv(zjdz_file, index=False, encoding='utf8')
        print('{} | 下载{}资金对帐表完成!'.format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                                         h_date.strftime('%Y-%m-%d')))


def download_jytj(h_date: datetime.date, is_date=False):
    print('{} | 开始下载{}交易统计表'.format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), h_date.strftime('%Y-%m-%d')))
    if is_date:
        jytj_file = pathlib.Path('{}/jytj_{}.csv'.format(h_date.strftime('%Y%m%d'), h_date.strftime('%Y%m%d')))
    else:
        jytj_file = pathlib.Path('交易统计表/jytj_{}.csv'.format(h_date.strftime('%Y%m%d')))
    if jytj_file.exists():
        print('{}交易统计表已存在!'.format(h_date.strftime('%Y-%m-%d')))
        return
    # 交易日期=年月
    with MSSQLProxy() as c:
        c.execute("""
            select
                   交易日期 as ex_date,客户号 as cust_num,客户名称 as cust_name,交易所 as exl_name,品种 as variety_name,
                   手数 as hands,成交额 as ex_amount,平今手数 as hands_close,平今成交额 as amount_close,持仓手数 as hands_holdings,
                   浮动盈亏 as profit_drift,总盈亏 as profit,净利润 as profit_net,保证金 as fund_bail,交易所保证金 as fund_bail_exl,
                   手续费 as charge,上交手续费 as charge_exl,留存手续费 as charge_odd,净手续费 as charge_net
            from
                 cust_jy_r
            where
                  交易日期=%s
            order by
                     cust_num;
        """, (h_date.strftime('%Y%m%d'),))
        data = list(c.fetchall())
        if len(data) < 1:
            print('无{}交易统计表数据!'.format(h_date.strftime('%Y-%m-%d')))
            return
        df = pd.DataFrame(data)
        df['cust_name'].fillna('', inplace=True)
        for c1 in ['ex_date', 'cust_num', 'cust_name', 'exl_name', 'variety_name']:
            df[c1] = df[c1].apply(lambda x: x.strip())
        for c2 in ['hands', 'ex_amount', 'hands_close', 'amount_close', 'hands_holdings', 'profit_drift', 'profit',
                   'profit_net', 'fund_bail', 'fund_bail_exl', 'charge', 'charge_exl', 'charge_odd', 'charge_net']:
            df[c2] = df[c2].astype(float)
        df.to_csv(jytj_file, index=False, encoding='utf8')
        print('{} | 下载{}交易统计表完成!'.format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                                         h_date.strftime('%Y-%m-%d')))


def download_cjmx(h_date: datetime.date, is_date=False):
    print('{} | 开始下载{}成交明细表'.format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), h_date.strftime('%Y-%m-%d')))
    if is_date:
        cjmx_file = pathlib.Path('{}/cjmx_{}.csv'.format(h_date.strftime('%Y%m%d'), h_date.strftime('%Y%m%d')))
    else:
        cjmx_file = pathlib.Path('成交明细表/cjmx_{}.csv'.format(h_date.strftime('%Y%m%d')))
    if cjmx_file.exists():
        print('{}成交明细表已存在!'.format(h_date.strftime('%Y-%m-%d')))
        return
    with MSSQLProxy() as c:
        c.execute("""
            select
                   交易日 as ex_date,客户号 as cust_num,客户名称 as cust_name,席位 as seat_name,
                   投保 as protect, 成交手数 as hands, 成交价格 as ex_price, 成交额 as ex_amount,
                   手续费 as charge, 委托类型 as wt_kind, 交易所 as exl_name, 品种 as variety_name,
                   交割期 as delivery, 买卖 as direction, 开平 as oc_kind,交易编码 as ex_num,
                   交易类别 as ex_kind, 合约号 as contract,强平标志 as close_flag
            from
                 chengjiao_list
            where
                  交易日=%s
            order by
                     cust_num;
        """, (h_date.strftime('%Y%m%d'),))
        data = list(c.fetchall())
        if len(data) < 1:
            print('无{}成交明细表数据!'.format(h_date.strftime('%Y-%m-%d')))
            return
        df = pd.DataFrame(data)
        df['cust_name'].fillna('', inplace=True)
        for c1 in ['ex_date', 'cust_num', 'cust_name', 'seat_name', 'protect', 'wt_kind', 'exl_name', 'variety_name',
                   'delivery', 'direction', 'oc_kind', 'ex_num', 'ex_kind', 'contract', 'close_flag']:
            df[c1] = df[c1].apply(lambda x: x.strip())
        for c2 in ['hands', 'ex_price', 'ex_amount', 'charge']:
            df[c2] = df[c2].astype(float)
        df.to_csv(cjmx_file, index=False, encoding='utf8')
        print('{} | 下载{}成交明细表完成!'.format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                                         h_date.strftime('%Y-%m-%d')))


def download_pcmx(h_date: datetime.date, is_date=False):
    print('{} | 开始下载{}平仓明细表'.format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), h_date.strftime('%Y-%m-%d')))
    if is_date:
        pcmx_file = pathlib.Path('{}/pcmx_{}.csv'.format(h_date.strftime('%Y%m%d'), h_date.strftime('%Y%m%d')))
    else:
        pcmx_file = pathlib.Path('平仓明细表/pcmx_{}.csv'.format(h_date.strftime('%Y%m%d')))
    if pcmx_file.exists():
        print('{}平仓明细表已存在!'.format(h_date.strftime('%Y-%m-%d')))
        return
    with MSSQLProxy() as c:
        c.execute("""
            select
                   平仓日期 as ex_date,客户号 as cust_num,客户名称 as cust_name,
                   平仓流水号 as clp_num,交易所 as exl_name, 席位 as seat_name,
                   交易类别 as ex_kind, 品种 as variety_name,交割期 as delivery,合约号 as contract,
                   买卖 as direction,手数 as hands,开仓价格 as price_open,平仓价格 as price_close,
                   平仓盈亏_浮 as profit_drift,平仓盈亏_盯 as profit_close,开仓日期 date_open,
                   开仓流水号 as  dto_num,交易编码 as ex_num,平仓类型 as cl_kind,
                   投保 as protect
            from
                 pingchang_list
            where
                  平仓日期=%s
            order by
                     cust_num;
        """, (h_date.strftime('%Y%m%d'),))
        data = list(c.fetchall())
        if len(data) < 1:
            print('无{}平仓明细表数据!'.format(h_date.strftime('%Y-%m-%d')))
            return
        df = pd.DataFrame(data)
        df['cust_name'].fillna('', inplace=True)
        for c1 in ['ex_date', 'cust_num', 'cust_name', 'clp_num', 'exl_name', 'seat_name', 'ex_kind', 'variety_name',
                   'delivery', 'contract', 'direction', 'date_open', 'dto_num', 'ex_num', 'cl_kind', 'protect']:
            df[c1] = df[c1].apply(lambda x: x.strip())
        for c2 in ['hands', 'price_open', 'price_close', 'profit_drift', 'profit_close']:
            df[c2] = df[c2].astype(float)
        df.to_csv(pcmx_file, index=False, encoding='utf8')
        print('{} | 下载{}平仓明细表完成!'.format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                                         h_date.strftime('%Y-%m-%d')))


def download_cjdgl(h_date: datetime.date, is_date=False):
    print('{} | 开始下载{}成交单管理'.format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), h_date.strftime('%Y-%m-%d')))
    if is_date:
        pcmx_file = pathlib.Path('{}/cjdgl_{}.csv'.format(h_date.strftime('%Y%m%d'), h_date.strftime('%Y%m%d')))
    else:
        pcmx_file = pathlib.Path('成交单管理/cjdgl_{}.csv'.format(h_date.strftime('%Y%m%d')))
    if pcmx_file.exists():
        print('{}成交单管理已存在!'.format(h_date.strftime('%Y-%m-%d')))
        return
    with CJDGLConnection() as c:
        c.execute("""
            select
                   交易日 as ex_date,成交时间 as ex_time,客户号 as cust_num,客户名称 as cust_name,
                   交易所 as exl_name,交易类别 as ex_kind,品种 as variety_name, 合约号 as contract,
                   买卖 as direction, 开平 as oc_kind,手数 as hands,成交价 as ex_price,
                   成交额 as ex_amount,手续费 as charge, 上交手续费 as ex_charge
            from
                 cj_mang
            where
                  交易日=%s
            order by
                     cust_num;
        """, (h_date.strftime('%Y%m%d'),))
        data = list(c.fetchall())
        if len(data) < 1:
            print('无{}成交单管理数据!'.format(h_date.strftime('%Y-%m-%d')))
            return
        df = pd.DataFrame(data)
        df['cust_name'].fillna('', inplace=True)
        for c1 in ['ex_date', 'ex_time', 'cust_num', 'cust_name', 'exl_name', 'ex_kind', 'variety_name',
                   'contract', 'direction', 'oc_kind']:
            df[c1] = df[c1].apply(lambda x: x.strip())
        for c2 in ['hands', 'ex_price', 'ex_amount', 'charge', 'ex_charge']:
            df[c2] = df[c2].astype(float)
        df.to_csv(pcmx_file, index=False, encoding='utf8')
        print('{} | 下载{}成交单管理完成!'.format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                                         h_date.strftime('%Y-%m-%d')))


if __name__ == '__main__':
    # make_folders()
    # for dt in generate_date(datetime.date(2022, 6, 29), datetime.date(2022, 6, 29)):
        # download_zjdz(dt)
        # download_jytj(dt)
        # download_cjmx(dt)
        # download_pcmx(dt)
        # download_cjdgl(dt)

    today = datetime.date.today()
    make_folders(today)
    download_zjdz(today)
    download_jytj(today)
    download_cjmx(today)
    download_pcmx(today)
    download_cjdgl(today)
